你的首个子查询解决方案
-
首先，我们需要按照日期和渠道分组。然后按事件数（第三列）排序，这样可以快速得出第一个问题的答案。
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC;
-
可以看出，要获得这一结果，提供了整个原始表格。查询的附加部分包括
*
，并且我们需要为表格设置别名。此外，是在
SELECT
语句中（而不是
FROM
）中提供表格。
SELECT *
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC) sub;
-
最后，我们在以下语句中能够获得显示每个渠道一天的平均事件数的表格。
SELECT channel, AVG(events) AS average_events
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2) sub
GROUP BY channel
ORDER BY 2 DESC;